package org.zsqx.tools;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.security.spec.ECField;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;

import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.zsqx.bean.CountTimes;
import org.zsqx.bean.PointsExcel;
import org.zsqx.bean.RainReportExcel;
import org.zsqx.bean.UsrReport;

import cDao.CdaoFactory;
import cDao.HelpDao;
import cDao.RealDao;

public class ExcelTools {
	private static String[] title = new String[] { "序号", "上报人", "灾情类型", "所在镇区",
			"灾情地点", "上报时间", "灾情描述", "来源", "灾情图片" };
	private static String fileName = new SimpleDateFormat("yyyyMMddHHmmss")
			.format(new Date());
	private static String imageUrl = "..\\meteorology_wap\\images\\upload\\";
	private static String excel_path = "\\login\\excel\\" + fileName + ".xls";

	public static String writeExcelByJXL(String absolute_path,
			List<UsrReport> list){
		WritableWorkbook wwb = null;
		String fileName = new SimpleDateFormat("yyyyMMddHHmmss")
				.format(new Date());
		String imageUrl = absolute_path
				+ "..\\meteorology_wap\\images\\upload\\";
		String excel_path = absolute_path + "\\login\\excel\\" + fileName
				+ ".xls";
		try {
			wwb = Workbook.createWorkbook(new File(excel_path));
			if (wwb != null) {
				WritableSheet ws = wwb.createSheet("中山气象灾情采集", 0);
				WritableFont wf = new WritableFont(WritableFont.ARIAL, 15,
						WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
						jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
				WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
				wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式
				int size = list.size();
				if (size > 0) {
					for (int i = 0; i < title.length; i++) {
						try {
							ws.setRowView(i + 1, 500);
							Label label1 = new Label(i, 0, title[i], wcf);
							ws.addCell(label1);
						} catch (RowsExceededException e) {
							e.printStackTrace();
						} catch (WriteException e) {
							e.printStackTrace();
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
					ws.setColumnView(1, 15);
					ws.setColumnView(2, 15);
					ws.setColumnView(3, 15);
					ws.setColumnView(4, 15);
					ws.setColumnView(5, 20);
					ws.setColumnView(6, 30);
					ws.setColumnView(7, 15);
					ws.setColumnView(8, 30);
					for (int i = 0; i < size; i++) {
						UsrReport report = list.get(i);
						try {
							Label label_reNum = new Label(0, i + 1, report
									.getReNum().toString());
							ws.addCell(label_reNum);
	
							Label label_reMan = new Label(1, i + 1,
									report.getReMan());
							ws.addCell(label_reMan);
	
							Label label_reType = new Label(2, i + 1,
									report.getReType());
							ws.addCell(label_reType);
	
							Label label_reTown = new Label(3, i + 1,
									report.getReTown());
							ws.addCell(label_reTown);
	
							Label label_reArea = new Label(4, i + 1,
									report.getReArea());
							ws.addCell(label_reArea);
							String reTime = "";
							if (null != report.getReTime()) {
								reTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
										.format(report.getReTime());
							}
							Label label_reTime = new Label(5, i + 1, reTime);
							ws.addCell(label_reTime);
	
							Label label_reStu = new Label(6, i + 1,
									report.getReStu());
							ws.addCell(label_reStu);
	
							Label label_reSrc = new Label(7, i + 1,
									report.getReSrc());
							ws.addCell(label_reSrc);
							File outputFile = null;
							if (null != report.getReImage()
									&& !"".equals(report.getReImage())) {
								File imgFile = new File(imageUrl
										+ report.getReImage());
								if (imgFile.exists() && imgFile.length() > 0) {
									// System.out.println("creating image...");
									BufferedImage input = null;
									try {
										input = ImageIO.read(imgFile);
										if (input != null) {
											String path = imgFile.getAbsolutePath();
											outputFile = new File(path.substring(0,
													path.lastIndexOf('.') + 1) + "png");
											ImageIO.write(input, "PNG", outputFile);
											if (outputFile.exists()
													&& outputFile.length() > 0) {
												WritableImage image = new WritableImage(
														8, i + 1, 1, 1, outputFile);//第一个参数为起始列，第二个为起始行，第三个为终止列，第四个为终止行。
												ws.setRowView(i + 1, 1800);
												ws.addImage(image);
											}
										}
									}catch (IOException e) {
										e.printStackTrace();
									} catch (Exception e) {
										e.printStackTrace();
									}
								} else {
									ws.setRowView(i + 1, 400);
									Label label_reImage = new Label(8, i + 1,
											"无上传图片");
									ws.addCell(label_reImage);
								}
							}
						} catch (RowsExceededException e) {
							e.printStackTrace();
						} catch (WriteException e) {
							e.printStackTrace();
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				}
				wwb.write();
				wwb.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return fileName;
	}

	public static String writeToExcel(String title, String absolute_path,
			List list, Map<String, String> map) throws NullPointerException,
			RowsExceededException, WriteException, IOException {
		String imageUrl = absolute_path + ExcelTools.imageUrl;
		String fileName= new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());;
		String excel_path = absolute_path + "\\login\\excel\\"
				+ fileName
				+ ".xls";
		WritableWorkbook wwb = Workbook.createWorkbook(new File(excel_path));
		WritableSheet ws = wwb.createSheet(title, 0);
		Class c = list.get(0).getClass();
		Field[] fileds = c.getDeclaredFields();
		int i = 0, j = 0;
		for (Field field : fileds) {
			if (map.containsKey(field.getName())) {
				String filedName = map.get(field.getName());
				Label label_reTime = new Label(i, 0, filedName);
				ws.addCell(label_reTime);
				i++;
			}
		}
		for (; j < list.size(); j++) {
			Object bean = list.get(j);
			int z = 0;
			for (Field field : fileds) {
				if (map.containsKey(field.getName())) {
					String methodName = HelpDao.getGetMethod(field.getName());
					Object value = HelpDao.methodInvoke(c, bean, methodName);
					String cellValue = "";
					// System.out.println(field.getName()+field.getType());
					if (field.getType() == Date.class) {
						// System.out.println("Date");
						if (null != value) {
							cellValue = new SimpleDateFormat(
									"yyyy-MM-dd HH:mm:ss").format(value);
						}
						Label label = new Label(z, j + 1, cellValue);
						ws.addCell(label);
					} else if (field.getType() == String.class
							&& map.get(field.getName()).indexOf("图片") != -1) {
						// System.out.println("pic");
						File outputFile = null;
						// System.out.println(imageUrl + value);
						File imgFile = new File(imageUrl + value);
						if (imgFile.exists() && imgFile.length() > 0) {
							BufferedImage input = null;
							try {
								input = ImageIO.read(imgFile);
							} catch (Exception e) {
								e.printStackTrace();
							}
							if (input != null) {
								String path = imgFile.getAbsolutePath();
								outputFile = new File(path.substring(0,
										path.lastIndexOf('.') + 1)
										+ "png");
								ImageIO.write(input, "PNG", outputFile);
								if (outputFile.exists()
										&& outputFile.length() > 0) {
									WritableImage image = new WritableImage(z,
											j + 1, 1, 1, outputFile);
									ws.addImage(image);
								}
							}
						} else {
							Label label_reImage = new Label(z, j + 1, "无上传图片");
							ws.addCell(label_reImage);
						}
					} else {
						// System.out.println("other");
						if (field.getType() != File.class && null != value) {
							cellValue = value.toString();
						}
						Label label = new Label(z, j + 1, cellValue);
						ws.addCell(label);
					}
					z++;
				}
			}
		}
		wwb.write();
		wwb.close();
		return fileName;
	}

	public static List<PointsExcel> getAllUsersPoints() {
		List<PointsExcel> list = new ArrayList<PointsExcel>();
		RealDao rd = CdaoFactory.getRealDao();
		PropertiesTools pt = new PropertiesTools();
		CountTimes countTimes = (CountTimes) pt
				.getPropertyesBean(new CountTimes());
		String sql = "select " + "lm_name,right_score,"
				+ "isnull(upwcount,0) as upwcount,isnull(upwcount*right_score*"
				+ countTimes.getUpwcount()
				+ ",0) as upwcountPoint,"
				+ "isnull(uplcount,0) as uplcount,isnull(uplcount*right_score*"
				+ countTimes.getUplcount()
				+ ",0) as uplcountPoint,"
				+ "isnull(upcount,0) as upcount,isnull(upcount*right_score*"
				+ countTimes.getUpcount()
				+ ",0) as upcountPoint,"
				+ "isnull(b3wcount,0) as b3wcount,isnull(b3wcount*right_score*"
				+ countTimes.getB3wcount()
				+ ",0) as b3wcountPoint,"
				+ "isnull(b3lcount,0) as b3lcount,isnull(b3lcount*right_score*"
				+ countTimes.getB3lcount()
				+ ",0) as b3lcountPoint,"
				+ "isnull(b3count,0) as b3count,isnull(b3count*right_score*"
				+ countTimes.getB3count()
				+ ",0) as b3countPoint,"
				+ "isnull(b2count,0) as b2count,isnull(b2count*right_score*"
				+ countTimes.getB2count()
				+ ",0) as b2countPoint,"
				+ "isnull(b4count,0) as b4count,isnull(b4count*right_score*"
				+ countTimes.getB4count()
				+ ",0) as b4countPoint,"
				+ "isnull(b5count,0) as b5count,isnull(b5count*right_score*"
				+ countTimes.getB5count()
				+ ",0) as b5countPoint,"
				+ "isnull(b6count,0) as b6count,isnull(b6count*right_score*"
				+ countTimes.getB6count()
				+ ",0) as b6countPoint,"
				+ "isnull(b7count,0) as b7count,isnull(b7count*right_score*"
				+ countTimes.getB7count()
				+ ",0) as b7countPoint,"
				+ "isnull(yu.logincount,0) as logincount,isnull(yu.logincount*right_score*"
				+ countTimes.getLogincount()
				+ ",0) as logincountPoint,"
				+ "isnull(eu.visitCount,0) as bbslogincount,isnull(eu.visitCount*right_score*"
				+ countTimes.getBbslogincount()
				+ ",0) as bbslogincountPoint,"
				+ "isnull(change_point,0) as extraPoint,"
				+ "isnull(last_point,0) as sumPoint "
				+ "from linkman "
				+ "left outer join Yj_User yu "
				+ "on lm_user=username "
				+ "left outer join linkman_type lt "
				+ "on lm_type_id=lt.id "
				+ "left outer join ejf_user eu " + "on lm_user=userID";
		// System.out.println(sql);
		ResultSet rs = rd.findBySql(sql, new ArrayList());
		try {
			while (rs.next()) {
				PointsExcel pe = new PointsExcel();
				pe.setLmName(rs.getString("lm_name"));
				pe.setRightScore(rs.getInt("right_score"));
				pe.setUpwcount(rs.getInt("upwcount"));
				pe.setUpwcountPoint(rs.getInt("upwcountPoint"));
				pe.setUplcount(rs.getInt("uplcount"));
				pe.setUplcountPoint(rs.getInt("uplcountPoint"));
				pe.setUpcount(rs.getInt("upcount"));
				pe.setUpcountPoint(rs.getInt("upcountPoint"));
				pe.setB3wcount(rs.getInt("b3wcount"));
				pe.setB3wcountPoint(rs.getInt("b3wcountPoint"));
				pe.setB3lcount(rs.getInt("b3lcount"));
				pe.setB3lcountPoint(rs.getInt("b3lcountPoint"));
				pe.setB3count(rs.getInt("b3count"));
				pe.setB3countPoint(rs.getInt("b3countPoint"));
				pe.setB2count(rs.getInt("b2count"));
				pe.setB2countPoint(rs.getInt("b2countPoint"));
				pe.setB4count(rs.getInt("b4count"));
				pe.setB4countPoint(rs.getInt("b4countPoint"));
				pe.setB5count(rs.getInt("b5count"));
				pe.setB5countPoint(rs.getInt("b5countPoint"));
				pe.setB6count(rs.getInt("b6count"));
				pe.setB6countPoint(rs.getInt("b6countPoint"));
				pe.setB7count(rs.getInt("b7count"));
				pe.setB7countPoint(rs.getInt("b7countPoint"));
				pe.setLogincount(rs.getInt("logincount"));
				pe.setLogincountPoint(rs.getInt("logincountPoint"));
				pe.setBbslogincount(rs.getInt("bbslogincount"));
				pe.setBbslogincountPoint(rs.getInt("bbslogincountPoint"));
				pe.setExtraPoint(rs.getInt("extraPoint"));
				pe.setSumPoint(rs.getInt("sumPoint"));
				list.add(pe);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public static List<RainReportExcel> getRainServReports(String startTime,String endTime,String orderFiled,String ordertype) {
		String sql="select id," +
				" (select Z_cname from zdz where Z_id = id) as zdz_Name," +
				" sum(hourrf) as dayrf," +
				" max(wd10maxdf) as wd10maxdf," +
				" max(wd3smaxdf) as wd3smaxdf" +
				" from zsqxdb.dbo.zdz_hours_data" +
				" where" +
				" datepart(minute,zdzdatetime)=0 and zdzdatetime" +
				" between '"+startTime+"' and '"+endTime+"'" +
				" group by id" +
				" order by "+orderFiled+" "+ordertype;
		System.out.println("导出Excel的SQL:"+sql);
		ResultSet rs = CdaoFactory.getRealDao().findBySql(sql, new ArrayList());
		List<RainReportExcel> list=new ArrayList<RainReportExcel>();
		try {
			while (rs.next()) {
				RainReportExcel r=new RainReportExcel();
				r.setId(rs.getString("id"));
				r.setZdz_Name(rs.getString("zdz_Name"));
				r.setDayrf(rs.getDouble("dayrf"));
				r.setWd10maxdf(rs.getDouble("wd10maxdf"));
				r.setWd3smaxdf(rs.getDouble("wd3smaxdf"));
				list.add(r);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

}
